<!--------------- CS143: Project 1B ----------------------------
--- Author: Chelsea Ju -----------------------------------------
--- Date: 2012/04/20 ------------------------------------------->

<HTML>
<HEAD><TITLE>CS143 Project 1B: Database Query</TITLE></HEAD>

<BODY>
<H1> CS143: Project 1B - Database Query </H1>

<FORM action="query.php" method="get">
   <b>Please type a single SQL query for the Movie Database:</b><br/>
   <TEXTAREA name = "sql_query" cols = 60 rows = 10><?php echo $_GET[sql_query]?></TEXTAREA><br/>
   <INPUT TYPE="submit" value="Submit Query"/><br/>
   <p>
   <font size=2>
   Note: use <i>show tables</i> to see the table names in database.<br/>
   use <i>describe tableName</i> to see the column names of a specific table.<br/>
   </font>
   </p>
</FORM>

   
<!------- display result here ------>
   <?php
   ini_set ("display_errors", 0); // hide the error message from webpage
   if($_GET[sql_query]){
     print "<b>Result From MySQL:</b>\n";

     // database connection
     $db_connection = mysql_connect("localhost", "cs143", "");
     if(!$db_connection){
       die("<p><font size=2 color = 'red'>".mysql_error()."</font></p>");
     }

     $db_database = mysql_select_db("CS143", $db_connection);
     if(!$db_database){
       die("<p><font size=2 color = 'red'>".mysql_error()."</font></p>");
     }

     // user query
     //     $input = $_GET[sql_query];
     //     $query = preg_replace("#[\r\n]#", " ", $input);
     //     $query = mysql_real_escape_string($query);
     $query = $_GET[sql_query];
     $result = mysql_query($query, $db_connection);

     print "<font size=2> (Issuing query <i> $query </i>)</font><br/>\n";

     if(!$result){
       die("<p><font size=2 color = 'red'>".mysql_error()."</font></p>");
     }

     // to handle select, describe or show queires
     if(mysql_num_fields($result) > 0){
       print "<p>\n";
       print "<TABLE border = 1px>\n";
       print "\t<TR>\n";
       
       // field name from the result
       $field_count = mysql_num_fields($result);
       for ($i=0; $i < $field_count; $i++){
	 print "\t\t<TH>";
	 print mysql_fetch_field($result, $i) -> name;
	 print "</TH>\n";
       }
       print "\t</TR>\n";
       
       // iterate rows to retrieve data
       while($row = mysql_fetch_row($result)){
	 print "\t<TR>\n";
	 for($i=0; $i < $field_count; $i++){
	   print "\t\t<TD align='middle'>";
	   print $row[$i];
	   print "</TD>\n";
	 }
	 print "\t</TR>\n";
       }
       
       print "</TABLE>\n";
       print "</p>\n";
     }

     // to handle delete or update query
     else{
       $affected = mysql_affected_rows($db_connection);
       print "<p>\n";
       print "<h4>Total affected rows: $affected</h4>\n";
       print "</p>\n";
     }

     mysql_free_result($result);
     mysql_close($db_connection);
   }
     
?>
     


</BODY>
</HTML>